package dbutils;

import com.sun.org.apache.xerces.internal.impl.xs.SchemaSymbols;

/**
 * Created by IntelliJ IDEA.
 * User: Admin
 * Date: Jun 28, 2010
 * Time: 10:26:19 AM
 * To change this template use File | Settings | File Templates.
 */
public class StringQueries {

    public static final String PHASE_START = " select distinct dial.name_ru, dp.name_ru from test_versions tv " +
            "    inner join test_parametrs_units tpu on tv.analysis_version = tpu.analysis_version_key " +
            "    inner join dic_analysis_instr_invest_list dial on tpu.analysis_instr_invest_list_id = dial.analysis_instr_invest_list_id " +
            "    inner join dic_phase dp on tv.phase_id = dp.phase_id " +
            "where 1 = 1    ";

    public static final String PHASES_APPENDIX = " %s tpu.analysis_instr_invest_list_id = %s  and tv.phase_id = %s ";

    public static final String PHASE_RETRIEVE = "select phase_id, name_ru, name_en, name_kk from dic_phase order by name_en asc";

    public static final String TEST_VERSION_INSERT = "INSERT INTO test_versions(analysis_version, phase_id)  VALUES(?, ?)";

    public static final String PHASE_ID = "select phase_id from groups where group_id = ?";

    public static final String VERSION_ID = "select distinct tpu.analysis_version_key as version from test_parametrs_units tpu where tpu.analysis_instr_invest_list_id = ? " +
            " and tpu.analysis_version_key in " +
            "(select tv.analysis_version from test_versions tv where tv.phase_id in " +
            "    (select phase_id from groups where group_id = ?))";

    public static final String FIELDS_OUT_OF_VERSION = "select analys_param from analys_translation where analys_table = (select analysis_instr_invest_list_id from dic_analysis_instr_invest_list where analysis_name = ?) and analys_param not in(%s)";

    public static final String TEST_TYPES = "select id, name_ru, name_en, name_kk from dic_test_type";

    public static final String PROBATIONER = "select probationer_id as probationerId, probationer_code as probationCode, date_visit_zero as vizitZero  from probationers where group_id = ? order by probationer_code ";

    public static final String VISIT_COUNT = "select count(*) from  groups_schema_visits gsv, schema_visit_time svt  where gsv.schema_visits_id = svt.schema_visits_id and gsv.groups_schema_visits_id = ?";

    public static final String ANALYSIS_NAMES = "select distinct ail.analysis_name, ail.analysis_instr_invest_list_id, ail.name_ru, ail.name_en, ail.name_kk, tpu.analysis_version_key " +
            "    from dic_analysis_instr_invest_list ail " +
            "        inner join test_parametrs_units tpu on ail.analysis_instr_invest_list_id = tpu.analysis_instr_invest_list_id " +
            "        inner join timetable tt on tt.analysis_version_key = tpu.analysis_version_key " +
            "        where tt.groups_schema_visits_id = ? ";

    public static final String TIME_TABLE = "select is_carried_out from timetable where groups_schema_visits_id = ?   and analysis_version_key = ?   and visit_id = ?";

    public static final String RESULT_PRESENT = "select vd.test_id from visit_details vd, %s t    where probationer_id = ? and visit_id = ? and groups_schema_visits_id = ?  and vd.test_id=t.test_id";

    public static final String TEST_ID = "select vd.test_id from visit_details vd where vd.probationer_id = ? and vd.visit_id = ? and vd.groups_schema_visits_id = ?";

    public static final String INSERT_TEST = "INSERT INTO visit_details(probationer_id, visit_id, user, insert_datetime, groups_schema_visits_id) " +
            "VALUES (?, ?, ?, ?, ?)";

    public static final String INSERT_INTO_VISIT_DETAILS = "INSERT INTO visit_details(test_id, probationer_id, visit_id, user, insert_datetime, groups_schema_visits_id) " +
            "VALUES (?, ?, ?, ?, ?, ?)";

    public static final String TEST_TYPE = "SELECT id, name_ru, name_en, name_kk FROM dic_test_type where id = ?";

    public static final String ANALYSIS = "SELECT analysis_instr_invest_list_id, name_ru, name_en, name_kk FROM dic_analysis_instr_invest_list " +
            "where test_type_id = ?";

    public static final String MAX_SCHEMA_VISIT = "SELECT max(schema_visits_id) FROM schema_visits";

    public static final String ANALYSIS_VERSIONS = "select distinct dail.analysis_instr_invest_list_id, name_ru, name_en, name_kk, tpu.analysis_instr_invest_list_id  from dic_analysis_instr_invest_list dail " +
            " left outer join test_parametrs_units tpu on dail.analysis_instr_invest_list_id = tpu.analysis_instr_invest_list_id";

    public static final String DISTINCT_VERSION = "select distinct tpu.version as version, dv.name_ru as nameRu, dv.name_en as nameEn, dv.name_kk as nameKz from test_parametrs_units tpu " +
            " inner join dic_version dv on tpu.version = dv.version_id where analysis_instr_invest_list_id = ? ";

    public static final String ANALYSIS_VERSION_KEY = "select distinct analysis_version_key from test_parametrs_units where analysis_instr_invest_list_id = ? " +
            " and version = ?";

    public static final String SCHEMA = "select gsv.groups_schema_visits_id" +
            ", name_ru, name_en, name_kk from schema_visits sv inner join  groups_schema_visits gsv on sv.schema_visits_id = gsv.schema_visits_id " +
            " where gsv.groups_schema_visits_id IN (SELECT groups_schema_visits_id " +
            " FROM groups_test_type WHERE group_id=?)AND dic_test_type_id=?";

    public static final String TEST_OF_PARAMETERS_UNITS = "SELECT parameter_id,  analys_table, daiil.analysis_name AS tableName, analys_param, at.name_ru, at.name_en,  at.name_kk, unit, " +
            " min_value, max_value, order_of_param FROM analys_translation AS at " +
            "INNER JOIN dic_analysis_instr_invest_list AS daiil ON at.analys_table=daiil.analysis_instr_invest_list_id " +
            "WHERE at.parameter_id NOT IN(SELECT parameter_id FROM test_parametrs_units)";

    public static final String TEST_OF_ANALYSIS_TRANSLATION = "select  c.TABLE_NAME, c.COLUMN_NAME, atrans.name_ru, atrans.name_en, atrans.name_kk from information_schema.columns AS c inner join dic_analysis_instr_invest_list dail on " +
            " dail.analysis_name = c.TABLE_NAME left outer join analys_translation atrans on atrans.analys_table = dail.analysis_instr_invest_list_id and atrans.analys_param = COLUMN_NAME where 1 = 1 " +
            " and c.COLUMN_NAME not in ('test_id', 'user', 'insert_datetime' " +
            " , 'modified_date', 'modified_user', 'exceed_max_min', 'version_id') " +
            " and   (atrans.name_ru is null OR atrans.name_en is null OR atrans.name_kk is null)";

    public static final String PHASES = "select distinct dp.phase_id, dp.name_ru as name_ru" +
            "   , dp.name_en as name_en, dp.name_kk as name_kk from dic_test_type dtt " +
            "    inner join groups_schema_visits gsv on dtt.ID = gsv.dic_test_type_id    " +
            "    inner join groups_test_type gtt on gsv.groups_schema_visits_id = gtt.groups_schema_visits_id " +
            "    inner join groups g on gtt.group_id = g.group_id " +
            "    inner join dic_phase dp on g.phase_id = dp.phase_id " +
            "    where dtt.ID = ?";

    public static final String SCHEMA_BY_PROBATIONER = "select gsv.groups_schema_visits_id " +
            "               , sv.name_ru as name_ru " +
            "                , sv.name_en as name_en " +
            "                , sv.name_kk as name_kk " +
            "             from probationers p " +
            "                inner join groups g on p.group_id = g.group_id " +
            "                inner join groups_test_type gtt on g.group_id = gtt.group_id " +
            "                inner join groups_schema_visits gsv on gsv.groups_schema_visits_id = gtt.groups_schema_visits_id " +
            "                inner join schema_visits sv on sv.schema_visits_id = gsv.schema_visits_id " +
            "                where 1 = 1 " +
            "              and p.probationer_id = ?" +
            "              and gsv.dic_test_type_id = ? ";



    public static final String PROBATIONER_BY_PHASE = "select p.probationer_code as probationCode" +
            "   , p.probationer_id as probationerId" +
            "   , p.date_visit_zero as vizitZero " +
            "       from dic_test_type dtt " +
            "    inner join groups_schema_visits gsv on dtt.ID = gsv.dic_test_type_id    " +
            "    inner join groups_test_type gtt on gsv.groups_schema_visits_id = gtt.groups_schema_visits_id " +
            "    inner join groups g on gtt.group_id = g.group_id " +
            "    inner join dic_phase dp on g.phase_id = dp.phase_id " +
            "    inner join probationers p on p.group_id = g.group_id " +
            "    where 1 = 1 " +
            "        and dtt.ID = ? " +
            "        and dp.phase_id = ? " +
            "        and gsv.groups_schema_visits_id = ?";

    public static final String PROBATIONER_BY_PHASE_TYPE = "select distinct p.probationer_code as probationCode " +
            "    , p.probationer_id as probationerId " +
            "    , p.date_visit_zero as vizitZero " +
            "    , g.group_id as group_id " +
            " from probationers p " +
            "    inner join groups g on p.group_id = g.group_id " +
            "    inner join groups_test_type gtt on g.group_id = gtt.group_id " +
            "    inner join groups_schema_visits gsv on gsv.groups_schema_visits_id = gtt.groups_schema_visits_id " +
            "    where 1 = 1 " +
            "    and g.phase_id = ? " +
            "    and gsv.dic_test_type_id = ? ORDER BY probationCode";

    public static final String TESTS_BY_PHASE="SELECT " +
            " dic_analysis_instr_invest_list.analysis_name," +
            " dic_analysis_instr_invest_list.name_ru," +
            " analys_translation.analys_param," +
            " analys_translation.name_ru FROM analys_translation" +
            " INNER JOIN dic_analysis_instr_invest_list" +
            " ON(analys_translation.analys_table=dic_analysis_instr_invest_list.analysis_instr_invest_list_id)" +
            " INNER JOIN test_parametrs_units" +
            " ON(analys_translation.parameter_id=test_parametrs_units.parameter_id)" +
            " WHERE test_parametrs_units.analysis_version_key " +
            " IN(  " +
            " SELECT  DISTINCT analysis_version_key  FROM timetable " +
            " WHERE " +
            " timetable.groups_schema_visits_id IN(" +
            " SELECT groups_schema_visits.groups_schema_visits_id " +
            " FROM groups " +
            " INNER JOIN groups_test_type" +
            " ON(groups.group_id=groups_test_type.group_id)" +
            " INNER JOIN groups_schema_visits" +
            " ON(groups_test_type.groups_schema_visits_id=groups_schema_visits.groups_schema_visits_id)" +
            " WHERE groups.phase_id IN($phaseVar)" +
            " )" +
            " AND timetable.is_carried_out=1" +
            " )" +
            " GROUP BY analysis_name";

    public static final String TEST_TIME="SELECT\n" +
            " schema_visits_id," +
            " visit_id, " +
            " CASE" +
            " WHEN visit_id=1 AND time_mls=0 THEN 'beforeTakingDrug' " +
            " WHEN visit_id=2 AND time_mls=0 THEN 'takingDrug' " +
            " WHEN time_mls >0 AND time_mls<86400 THEN CONCAT('hour ',(time_mls/3600)) " +
            " ELSE CONCAT('day ',ROUND((time_mls/86400),0)) " +
            " END " +
            " AS testTime " +
            " FROM schema_visit_time WHERE schema_visits_id  " +
            " IN(SELECT schema_visits_id FROM groups_schema_visits WHERE groups_schema_visits_id=?) " +
            " AND visit_id=?";

}
